﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using MySql.Data; //needed for mysql connector
using MySql.Data.MySqlClient;

namespace Genealogy_Builder
{
    public partial class SelectDialog : Form
    {
        public MySqlCommand cmd;
        private bool selectFromCurrent;

        public SelectDialog(List<Trait> availableTraits, List<Place> availablePlaces)
        {
            InitializeComponent();
            clbTraits.DataSource = availableTraits;
            clbFrom.DataSource = availablePlaces;

            dateBornAfter.CustomFormat = "yyyy-MM-dd";
            dateBornBefore.CustomFormat = "yyyy-MM-dd";
            dateDiedAfter.CustomFormat = "yyyy-MM-dd";
            dateDiedBefore.CustomFormat = "yyyy-MM-dd";

            cmd = null;
            selectFromCurrent = false;
        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void SelectDialog_Load(object sender, EventArgs e)
        {

        }

        private void checkedListBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void dateBornBefore_ValueChanged(object sender, EventArgs e)
        {
            chkBornBefore.Checked = true;
        }

        private void dateBornAfter_ValueChanged(object sender, EventArgs e)
        {
            chkBornAfter.Checked = true;
        }

        private void dateDiedBefore_ValueChanged(object sender, EventArgs e)
        {
            chkDiedBefore.Checked = true;
        }

        private void dateDiedAfter_ValueChanged(object sender, EventArgs e)
        {
            chkDiedAfter.Checked = true;
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            //clear the date parameters
            chkBornBefore.Checked = false;
            chkBornAfter.Checked = false;
            chkDiedAfter.Checked = false;
            chkDiedBefore.Checked = false;

            //clear the trait parameters
            foreach (int Index in clbTraits.CheckedIndices)
            {
                clbTraits.SetItemChecked(Index, false);
            }
        }

        private void makeSelectionString()
        {
            StringBuilder cmdString = new StringBuilder("SELECT Person.person_id FROM Person WHERE (");
            List<string> paramList = new List<string>();

            bool first = true;
            foreach (Place p in clbFrom.CheckedItems)
            {
                if (!first)
                {
                    cmdString.Append(" OR ");
                }
                first = false;
                cmdString.Append("Person.`from` = @param" + paramList.Count.ToString());
                paramList.Add(p.place_id.ToString());
            }
            if (clbFrom.CheckedItems.Count == 0)
                cmdString.Append("1");
            cmdString.Append(")");

            if (clbTraits.CheckedItems.Count != 0)
            {
                cmdString.Append(" AND Person.person_id IN (SELECT person FROM HasTraits WHERE ");
                //loop through traits, create the needed sql
                first = true;
                foreach (Trait t in clbTraits.CheckedItems)
                {
                    if (!first)
                    {
                        cmdString.Append(" OR ");
                    }
                    first = false;
                    cmdString.Append("trait = @param" + paramList.Count.ToString());
                    paramList.Add(t.trait_name);
                }

                cmdString.Append(")");
            }

            if (chkBornAfter.Checked || chkBornBefore.Checked || chkDiedAfter.Checked || chkDiedBefore.Checked)
            {
                cmdString.Append(" AND Person.person_id IN (SELECT person_id FROM Person WHERE ");

                first = true;

                //construct any needed date parameters
                if (chkBornAfter.Checked)
                {
                    first = false;
                    cmdString.Append("date_of_birth >= '" + dateBornAfter.Value.Year + "-" + dateBornAfter.Value.Month + "-" + dateBornAfter.Value.Day + "'");

                    Console.WriteLine("People born after: " + dateBornAfter.Value);
                }
                if (chkBornBefore.Checked)
                {
                    if (!first)
                    {
                        cmdString.Append(" AND ");
                    }
                    first = false;
                    cmdString.Append("date_of_birth <= '" + dateBornBefore.Value.Year + "-" + dateBornBefore.Value.Month + "-" + dateBornBefore.Value.Day + "'");
                    DateTime d = dateBornBefore.Value;

                    paramList.Add(d.Year + "-" + d.Month + "-" + d.Day);

                    Console.WriteLine("People born before: " + dateBornBefore.Value);
                }
                if (chkDiedAfter.Checked)
                {
                    if (!first)
                    {
                        cmdString.Append(" AND ");
                    }
                    first = false;
                    cmdString.Append("date_of_death >= '" + dateDiedAfter.Value.Year + "-" + dateDiedAfter.Value.Month + "-" + dateDiedAfter.Value.Day + "'");
                    DateTime d = dateDiedAfter.Value;

                    paramList.Add(d.Year + "-" + d.Month + "-" + d.Day);
                    Console.WriteLine("People died after: " + dateDiedAfter.Value);
                }
                if (chkDiedBefore.Checked)
                {
                    if (!first)
                    {
                        cmdString.Append(" AND ");
                    }
                    first = false;
                    cmdString.Append("date_of_death <= '" + dateDiedBefore.Value.Year + "-" + dateDiedBefore.Value.Month + "-" + dateDiedBefore.Value.Day + "'");
                    DateTime d = dateDiedBefore.Value;

                    paramList.Add(d.Year + "-" + d.Month + "-" + d.Day);
                    Console.WriteLine("People died before: " + dateDiedBefore.Value);
                }
                cmdString.Append(")");
            }

            cmd.CommandText = cmdString.ToString();
            cmd.Prepare();
            for (int i = 0; i < paramList.Count; i++)
            {
                cmd.Parameters.AddWithValue("param" + i.ToString(), paramList[i]);
                Console.WriteLine("param" + i.ToString() + " = " + paramList[i]);
            }
        }

        public bool ShouldSelectFromCurrent()
        {
            return selectFromCurrent;
        }

        private void btnSelect_Click(object sender, EventArgs e)
        {
            selectFromCurrent = false;
            makeSelectionString();

            //close out so that the main window can process the results
            this.Close();
        }

        private void btnSelectFromCurrent_Click(object sender, EventArgs e)
        {
            selectFromCurrent = true;
            makeSelectionString();

            this.Close();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            if (cmd != null)
            {
                cmd.Dispose();
                cmd = null;
            }
            this.Close();
        }
    }
}
